package edu.ues21.tcs.ecommerce2011.mvc.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import org.springframework.transaction.annotation.Transactional;

/**
 *
 * @author fmantaras
 */
@Transactional
public class ReporteDAOImpl implements ReporteDAO {

    Connection c = null;
    ResultSet rs = null;
    Statement stmt = null;

    public ReporteDAOImpl() {
    }

    public List<List<Object>> masVendido(){
		 List<List<Object>> reporte=new ArrayList<List<Object>>();
		try {
			 c=MySqlDb.getConnection();
            stmt=c.createStatement();
			rs = stmt.executeQuery("SELECT Cuit,RazonSocial,a.nombre,Propiedades,sum(cantidad) Cantidad " +
					"FROM detallepedido d " +
					"inner join pedido p on d.pedido = p.idPedido " +
					"inner join articuloPrecio ap on d.articuloprecio = ap.idarticuloprecio " +
					"inner join articulo a on ap.articulo = a.idarticulo " +
					"inner join cliente c on p.cliente = c.cuit "+
					"group by p.cliente,ap.articulo order by Cantidad desc");
			while(rs.next()){
				List<Object> reporte2=new ArrayList<Object>();
				reporte2.add(rs.getInt("Cuit"));
				reporte2.add(rs.getString("RazonSocial"));
				reporte2.add(rs.getString("Nombre"));
				reporte2.add(rs.getString("Propiedades"));
				reporte2.add(rs.getInt("Cantidad"));
				reporte.add(reporte2);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		 finally{
            try {
           	 rs.close();
                stmt.close();
                c.close();
            } catch (Exception e) {
                System.out.println(e.getMessage());
                e.printStackTrace();
            }
        }
		return reporte;
	}
    public List<List<Object>> marcaMasVendida(){
		 List<List<Object>> reporte=new ArrayList<List<Object>>();
		try {
			 c=MySqlDb.getConnection();
           stmt=c.createStatement();
			rs = stmt.executeQuery("SELECT Cuit,RazonSocial,m.nombre,sum(cantidad) Cantidad FROM detallepedido d inner join pedido p on d.pedido = p.idPedido inner join articuloPrecio ap on d.articuloprecio = ap.idarticuloprecio inner join articulo a on ap.articulo = a.idarticulo  inner join marca m on a.marca = m.idmarca inner join cliente c on p.cliente = c.cuit group by p.cliente,m.idmarca order by Cantidad desc");
			while(rs.next()){
				List<Object> reporte2=new ArrayList<Object>();
				reporte2.add(rs.getInt("Cuit"));
				reporte2.add(rs.getString("RazonSocial"));
				reporte2.add(rs.getString("Nombre"));
				reporte2.add(rs.getInt("Cantidad"));
				reporte.add(reporte2);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		 finally{
           try {
          	 rs.close();
               stmt.close();
               c.close();
           } catch (Exception e) {
               System.out.println(e.getMessage());
               e.printStackTrace();
           }
       }
		return reporte;
	}
}
